package com.ruoyi.tongji.run;

import com.ruoyi.tongji.run.manager.ManagerFactory;
import com.ruoyi.tongji.utils.DateUtil;
import org.beetl.sql.core.*;
import org.beetl.sql.core.db.DBStyle;
import org.beetl.sql.core.db.MySqlStyle;
import org.beetl.sql.ext.DebugInterceptor;

import java.io.File;
import java.util.*;

public class 统计14天到期20天内复采率 {



    public static String root = "E:\\文档\\2023\\考核记录\\程序生成\\20天内复采率";

    public static void main(String[] args) {
        SQLManager sqlManager = ManagerFactory.jiangzhanSqlManager();


        SQLManager taibangSqlManager = ManagerFactory.taibangSqlManager();

        List tongjiList = new ArrayList();

        File rootFile = new File(root);
        rootFile.mkdirs();

        String day = "2023-02-01";
        String dateStrStart = day + " 00:00:00";
        String dateStrEnd = day + " 23:59:00";
        Integer days = 150;

        Date startDate = DateUtil.getDate(day,"yyyy-MM-dd",0);
        Date endDate = DateUtil.getDate(day,"yyyy-MM-dd",days - 1);

        List laojiangyuankaoheList = new ArrayList();
        Map data = new HashMap();
        Integer i = 0;
        while (i < days ) {
            i = i + 1;
            Date caijiangDateStart = DateUtil.getDate(dateStrStart, "yyyy-MM-dd HH:mm:ss", i-1);
            Date caijiangDateEnd = DateUtil.getDate(dateStrEnd, "yyyy-MM-dd HH:mm:ss", i-1);

            Map fucailvkaohe = new HashMap();
            fucailvkaohe.put("day",DateUtil.format(caijiangDateStart,"yyyy/MM/dd"));
            //当天采浆人员列表
            List<HashMap> caijiangList = taibangSqlManager.execute(new SQLReady("select * from (\n" +
                    "select d.donorNo 浆员编号, \n" +
                    "d.donorName 浆员姓名,\n" +
                    "d.phone 联系电话, area.cname as 区域名称, d.codeDonorState as 状态, \n" +
                    "DATE_FORMAT(c.beginDate,'%Y-%m-%d') as 本次采浆日期, \n" +
                    "DATE_FORMAT(d.plasmaDate,'%Y-%m-%d')  as 最后采浆日期 ,\n" +
                    "d.remark from t_bims_donor_register r left join t_bims_plasma_collection c on r.donorId = c.donorId and LEFT(r.registerDate,8) = LEFT(c.beginDate,8) left join t_bims_donor d on c.donorId = d.donorId left join t_bims_linkman l on r.linkmanId = l.linkmanId left join t_bims_code_area as area on area.ccode = d.codeArea \n" +
                    "where r.registerDate between ? and ? and c.weight > 0 and l.linkmanId is null \n" +
                    ") dd  ",
                    new Object[]{DateUtil.format(caijiangDateStart, "yyyyMMddHHmmsss"), DateUtil.format(caijiangDateEnd, "yyyyMMddHHmmsss")}), HashMap.class);
            Integer caijiangCount = caijiangList.size();
            fucailvkaohe.put("caijiangCount",caijiangCount);
            //14天复采量
            Date fucai14DateRangeStart = DateUtil.getDate(dateStrStart, "yyyy-MM-dd HH:mm:ss", i-1+14);
            Date fucai14DateRangeEnd = DateUtil.getDate(dateStrEnd, "yyyy-MM-dd HH:mm:ss", i-1+14);
            //14天复采人数
            List<HashMap> fucai14tianList = taibangSqlManager.execute(new SQLReady("select * from (\n" +
                    "select d.donorNo 浆员编号, \n" +
                    "d.donorName 浆员姓名,\n" +
                    "d.phone 联系电话, area.cname as 区域名称, d.codeDonorState as 状态, \n" +
                    "DATE_FORMAT(c.beginDate,'%Y-%m-%d') as 本次采浆日期, \n" +
                    "DATE_FORMAT(d.plasmaDate,'%Y-%m-%d')  as 最后采浆日期 ,\n" +
                    "d.remark from t_bims_donor_register r left join t_bims_plasma_collection c on r.donorId = c.donorId and LEFT(r.registerDate,8) = LEFT(c.beginDate,8) left join t_bims_donor d on c.donorId = d.donorId left join t_bims_linkman l on r.linkmanId = l.linkmanId left join t_bims_code_area as area on area.ccode = d.codeArea \n" +
                    "where r.registerDate between ? and ? and c.weight > 0 and d.donorNo in (" +
                    "" +
                    "" +
                    "select 浆员编号 from (\n" +
                    "select d.donorNo 浆员编号, \n" +
                    "d.donorName 浆员姓名,\n" +
                    "d.phone 联系电话, \n" +
                    "area.cname as 区域名称,d.codeDonorState as 状态,\n" +
                    "d.cancelSystem as cancelSystem,\n" +
                    "DATE_FORMAT(c.beginDate,'%Y-%m-%d') as 本次采浆日期, \n" +
                    "DATE_FORMAT(d.plasmaDate,'%Y-%m-%d')  as 最后采浆日期 ,\n" +
                    "d.remark from t_bims_donor_register r left join t_bims_plasma_collection c on r.donorId = c.donorId and LEFT(r.registerDate,8) = LEFT(c.beginDate,8) left join t_bims_donor d on c.donorId = d.donorId left join t_bims_linkman l on r.linkmanId = l.linkmanId left join t_bims_code_area as area on area.ccode = d.codeArea\n" +
                    "where r.registerDate between ? and ? and c.weight > 0 and l.linkmanId is null \n" +
                    ") d  " +
                    "" +
                    "" +
                    ") \n" +
                    ") dd  ",
                    new Object[]{DateUtil.format(caijiangDateStart, "yyyyMMddHHmmsss"), DateUtil.format(caijiangDateEnd, "yyyyMMddHHmmsss"),DateUtil.format(fucai14DateRangeStart, "yyyyMMddHHmmsss"), DateUtil.format(fucai14DateRangeEnd, "yyyyMMddHHmmsss")}), HashMap.class);
            Integer caijiang14tianCount = fucai14tianList.size();
            fucailvkaohe.put("caijiang14tianCount",caijiang14tianCount);
            //20天复采量
            Date xinjiangyuan20fucaiDateRangeStart = DateUtil.getDate(dateStrStart, "yyyy-MM-dd HH:mm:ss", i-1+14);
            Date xinjiangyuan20fucaiDateRangeEnd = DateUtil.getDate(dateStrEnd, "yyyy-MM-dd HH:mm:ss", i-1+20);
            //20天内复采列表
            List<HashMap> fucai20tianneiList = taibangSqlManager.execute(new SQLReady("select * from (\n" +
                    "select d.donorNo 浆员编号, \n" +
                    "d.donorName 浆员姓名,\n" +
                    "d.phone 联系电话, area.cname as 区域名称, d.codeDonorState as 状态, \n" +
                    "DATE_FORMAT(c.beginDate,'%Y-%m-%d') as 本次采浆日期, \n" +
                    "DATE_FORMAT(d.plasmaDate,'%Y-%m-%d')  as 最后采浆日期 ,\n" +
                    "d.remark from t_bims_donor_register r left join t_bims_plasma_collection c on r.donorId = c.donorId and LEFT(r.registerDate,8) = LEFT(c.beginDate,8) left join t_bims_donor d on c.donorId = d.donorId left join t_bims_linkman l on r.linkmanId = l.linkmanId left join t_bims_code_area as area on area.ccode = d.codeArea \n" +
                    "where r.registerDate between ? and ? and c.weight > 0 and d.donorNo in (" +
                    "" +
                    "" +
                    "select 浆员编号 from (\n" +
                    "select d.donorNo 浆员编号, \n" +
                    "d.donorName 浆员姓名,\n" +
                    "d.phone 联系电话, \n" +
                    "area.cname as 区域名称,d.codeDonorState as 状态,\n" +
                    "d.cancelSystem as cancelSystem,\n" +
                    "DATE_FORMAT(c.beginDate,'%Y-%m-%d') as 本次采浆日期, \n" +
                    "DATE_FORMAT(d.plasmaDate,'%Y-%m-%d')  as 最后采浆日期 ,\n" +
                    "d.remark from t_bims_donor_register r left join t_bims_plasma_collection c on r.donorId = c.donorId and LEFT(r.registerDate,8) = LEFT(c.beginDate,8) left join t_bims_donor d on c.donorId = d.donorId left join t_bims_linkman l on r.linkmanId = l.linkmanId left join t_bims_code_area as area on area.ccode = d.codeArea\n" +
                    "where r.registerDate between ? and ? and c.weight > 0 and l.linkmanId is null \n" +
                    ") d  " +
                    "" +
                    "" +
                    ") \n" +
                    ") dd  ",
                    new Object[]{DateUtil.format(caijiangDateStart, "yyyyMMddHHmmsss"), DateUtil.format(caijiangDateEnd, "yyyyMMddHHmmsss"),DateUtil.format(xinjiangyuan20fucaiDateRangeStart, "yyyyMMddHHmmsss"), DateUtil.format(xinjiangyuan20fucaiDateRangeEnd, "yyyyMMddHHmmsss")}), HashMap.class);
            Integer fucai20tianneiCount = fucai20tianneiList.size();
            fucailvkaohe.put("fucai20tianneiCount",fucai20tianneiCount);

            laojiangyuankaoheList.add(fucailvkaohe);
        }
        data.put("laojiangyuankaoheList",laojiangyuankaoheList);

        ManagerFactory.createExcel(root,
                "excelTemplates/员工/14天、20天内复采考核统计.xls",
                "\\" + DateUtil.format(startDate, "yyyy年MM月dd日") + "-" + DateUtil.format(endDate, "yyyy年MM月dd日") + ".xls",data);
    }
}
